using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Commons {
	class PropertyDA {

        private const string SP_CountByPropertyCategoryID = "Property_CountByPropertyCategoryID";
        private const string SP_GetByID = "Property_GetByID";
        private const string SP_GetByPropertyCategoryIDWithPaging = "Property_GetByPropertyCategoryIDWithPaging";
        private const string SP_Insert = "Property_Insert";
        private const string SP_Update = "Property_Update";

		public static PropertyEnt CreateFromReader(IDataReader reader) {
			PropertyEnt item = new PropertyEnt();
			item.ID = ConvertUtils.ToInt(reader["ID"]);
			item.Name = ConvertUtils.ToString(reader["Name"]);
			item.FileName = ConvertUtils.ToString(reader["FileName"]);
			item.PropertyCategoryID = ConvertUtils.ToShort(reader["PropertyCategoryID"]);
			item.PointPrice = ConvertUtils.ToShort(reader["PointPrice"]);
			item.CoinPrice = ConvertUtils.ToShort(reader["CoinPrice"]);
            item.PaymentMethods = (EPropertyPaymentMethod)ConvertUtils.ToByte(reader["PaymentMethods"]);
            item.Status = (EPropertyStatus)ConvertUtils.ToByte(reader["Status"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			item.DateModified = ConvertUtils.ToDateTime(reader["DateModified"]);
			return item;
        }

        public static int CountByPropertyCategoryID(short propertyCategoryID, string connectionName) {
            int result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountByPropertyCategoryID)) {
                db.AddParameter(cmd, "PropertyCategoryID", propertyCategoryID);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static PropertyEnt GetByID(int iD, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            PropertyEnt result = null;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetByID)) {
                db.AddParameter(cmd, "ID", iD);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = CreateFromReader(reader);
                    }
                }
            }
            return result;
        }

        public static List<PropertyEnt> GetByPropertyCategoryIDWithPaging(short propertyCategoryID, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<PropertyEnt> result = new List<PropertyEnt>();
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetByPropertyCategoryIDWithPaging)) {
                db.AddParameter(cmd, "PropertyCategoryID", propertyCategoryID);
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        public static int Insert(PropertyEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Insert)) {
                db.AddParameterOuter(cmd, "ID", DbType.Int32);
                db.AddParameter(cmd, "Name", item.Name);
                db.AddParameter(cmd, "FileName", item.FileName);
                db.AddParameter(cmd, "PropertyCategoryID", item.PropertyCategoryID);
                db.AddParameter(cmd, "PointPrice", item.PointPrice);
                db.AddParameter(cmd, "CoinPrice", item.CoinPrice);
                db.AddParameter(cmd, "PaymentMethods", (byte)item.PaymentMethods);
                db.AddParameter(cmd, "Status", item.Status);
                db.AddParameter(cmd, "DateCreated", item.DateCreated);
                db.AddParameter(cmd, "DateModified", item.DateModified);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    item.ID = ConvertUtils.ToInt(cmd.Parameters["ID"].Value);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    item.ID = ConvertUtils.ToInt(cmd.Parameters["ID"].Value);
                }
            }
            return result;
        }

        public static int Update(PropertyEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Update)) {
                db.AddParameter(cmd, "ID", item.ID);
                db.AddParameter(cmd, "Name", item.Name);
                db.AddParameter(cmd, "FileName", item.FileName);
                db.AddParameter(cmd, "PropertyCategoryID", item.PropertyCategoryID);
                db.AddParameter(cmd, "PointPrice", item.PointPrice);
                db.AddParameter(cmd, "CoinPrice", item.CoinPrice);
                db.AddParameter(cmd, "PaymentMethods", (byte)item.PaymentMethods);
                db.AddParameter(cmd, "Status", item.Status);
                db.AddParameter(cmd, "DateModified", item.DateModified);
                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

	}
}